/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.LoaiSanPham;
import POJO.SanPham;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kim
 */
public class SanPhamDao {
	
	public static ArrayList<SanPham> layDanhSach() {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = "SELECT * FROM SanPham ";
		String sql = str;
		
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static int laySoLuongCoTheXuat(int MaSP) {
		int tmp = 0;
		String sql = String.format("Select  sp.SLTon -( dbo.soLuongCanXuat(%d"
			 + ") - dbo.soLuongDaXuat(%d) ) as  CTX from SanPham sp where sp.MaSP = %d ", MaSP, MaSP, MaSP);
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("CTX");
				//return ma;
				tmp = ma;
			}
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
	
	public int layMaMax() {
		int kq = 0;
		String sql = "Select Max(MaSP) as max from SanPham";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("max");
				//return ma;
				kq = ma;
			}
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		
		return kq;
	}
	
	public static int laySoDong() {
		String sql = "select count(MaSP) as COUN from SanPham ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;
		
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("COUN");
			}
		} catch (Exception ex) {
			Logger.getLogger(KhachHangDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return n;
	}
	
	public static ArrayList<SanPham> layDanhSachPhanTrang(int soDong, int trangSo) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBangPhanTrang("SanPham", "MaSP", soDong, trangSo);
			while (rs.next()) {
				int maSP = rs.getInt("MaSP");
				String tenSP = rs.getString("TenSP");
				int slTon = rs.getInt("SLTon");
				int slTonToiThieu = rs.getInt("SLTonToiThieu");
				int tgbh = rs.getInt("ThoiGianBaoHanh");
				String moTa = rs.getString("MoTa");
				Float giaBan = rs.getFloat("GiaBanHienHanh");
				int maLSP = rs.getInt("MaLSP");
				SanPham tmp = new SanPham(maSP, tenSP, slTon, slTonToiThieu, tgbh, moTa, giaBan, maLSP);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<SanPham> layDanhSachByMaLSPDeBan(int MaLSP) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = String.format("SELECT MaSP ,TenSP ,"
			 + " sp.SLTon -( dbo.soLuongCanXuat(sp.MaSP) - dbo.soLuongDaXuat(sp.MaSP)) as SLTon "
			 + ",ThoiGianBaoHanh ,MoTa ,GiaBanHienHanh  "
			 + "FROM SanPham sp Where sp.MaLSP= %d", MaLSP);
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(MaLSP);
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static ArrayList<SanPham> layDanhSachByMaLSP(int MaLSP) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = "SELECT MaSP ,TenSP ,SLTon ,SLTonToiThieu ,ThoiGianBaoHanh ,MoTa ,GiaBanHienHanh ,MaLSP "
			 + "FROM SanPham sp Where sp.MaLSP=" + MaLSP;
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static ArrayList<SanPham> layDanhSachByMaNhaCC(int MaNCC) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = "SELECT sp.MaSP ,TenSP ,SLTon ,SLTonToiThieu ,ThoiGianBaoHanh ,MoTa ,GiaBanHienHanh ,MaLSP "
			 + "FROM SanPham sp, KhaNangCungUng kn"
			 + " Where sp.MaSP = kn.MaSP AND kn.MaNCC = " + MaNCC;
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static ArrayList<SanPham> layDanhSachByMaNCCKhongCC(int MaNCC) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = " Select MaSP ,TenSP ,SLTon ,SLTonToiThieu ,ThoiGianBaoHanh ,MoTa ,GiaBanHienHanh ,MaLSP"
			 + " from SanPham"
			 + " where SanPham.MaSP not in("
			 + " SELECT sp.MaSP "
			 + " FROM SanPham sp, KhaNangCungUng kn "
			 + " Where sp.MaSP = kn.MaSP AND kn.MaNCC = " + MaNCC + ")";
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static SanPham layDanhSachByMaSP(int MaSP) {
		SanPham tmp = null;
		String str = " Select * from SanPham where MaSP = " + MaSP;
		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int maSP = rs.getInt("MaSP");
				String tenSP = rs.getString("TenSP");
				int slTon = rs.getInt("SLTon");
				int slTonToiThieu = rs.getInt("SLTonToiThieu");
				int tgbh = rs.getInt("ThoiGianBaoHanh");
				String moTa = rs.getString("MoTa");
				Float giaBan = rs.getFloat("GiaBanHienHanh");
				int maLSP = rs.getInt("MaLSP");
				tmp = new SanPham(maSP, tenSP, slTon, slTonToiThieu, tgbh, moTa, giaBan, maLSP);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
	
	public static ArrayList<SanPham> layDanhSachSanPhamCanNhap() {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = "SELECT * FROM SanPham sp Where sp.SLTon <= sp.SLTonToiThieu";
		String sql = str;
		
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static ArrayList<SanPham> layDanhSachSanPhamCanNhapByLoaiSanPham(LoaiSanPham tmp) {
		ArrayList<SanPham> ds = new ArrayList<SanPham>();
		String str = "SELECT * FROM SanPham sp Where sp.SLTon <= sp.SLTonToiThieu and  MaLSP = " + tmp.getMaLSP();
		String sql = str;
		
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPham sp = new SanPham();
				sp.setMaSP(rs.getInt("MaSP"));
				sp.setTenSP(rs.getString("TenSP"));
				sp.setSlTon(rs.getInt("SLTon"));
				sp.setSlTonToiThieu(rs.getInt("SLTonToiThieu"));
				sp.setThoiGianBaoHanh(rs.getInt("ThoiGianBaoHanh"));
				sp.setMoTa(rs.getString("MoTa"));
				sp.setGiaBanHienHanh(rs.getFloat("GiaBanHienHanh"));
				sp.setMaLSP(rs.getInt("MaLSP"));
				
				ds.add(sp);
			}
			
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
			
		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}
	
	public static boolean them(SanPham sp) {
		boolean kq = false;
		
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			Object[] ob = new Object[7];
			ob[0] = sp.getTenSP();
			ob[1] = sp.getSlTon();
			ob[2] = sp.getSlTonToiThieu();
			ob[3] = sp.getThoiGianBaoHanh();
			ob[4] = sp.getMoTa();
			ob[5] = sp.getGiaBanHienHanh();
			ob[6] = sp.getMaLSP();
			
			n = cn.thucThiProc("spThemSanPham", ob);
		} catch (SQLException ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		return kq;
	}
	
	public static boolean xoa(int maSP) {
		boolean kq = false;
		String str = "if not exists (select cthd.MaSP from CTHoaDon cthd where cthd.MaSP =" + maSP
			 + "  ) and not exists (select ctpxk.MaSp from CTPXuatKho ctpxk  where ctpxk.MaSP =" + maSP
			 + "  ) and not exists (select kn.MaSp from KhaNangCungUng kn  where kn.MaSP =" + maSP
			 + "  ) and not exists (select ctn.MaSp from CTPNhapKho ctn   where ctn.MaSP =" + maSP
			 + " )";
		String sql = String.format(str + " DELETE FROM SanPham WHERE MaSP=%d", maSP);
		int n;
		n = -1;
		KetNoi cn = null;
		try {
			cn = new KetNoi();
			
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		
		return kq;
	}
	
	public static boolean capNhat(SanPham sp) {
		boolean kq = false;
		KetNoi cn = null;
		String sql = String.format("UPDATE SanPham "
			 + "SET "
			 + "TenSP = N'%s', "
			 + "SLTonToiThieu = %d, "
			 + "ThoiGianBaoHanh = %d, "
			 + "MoTa = N'%s', "
			 + "GiaBanHienHanh = %f, "
			 + "MaLSP = %d "
			 + "WHERE  MaSP = %d; ", sp.getTenSP(), sp.getSlTonToiThieu(), sp.getThoiGianBaoHanh(), sp.getMoTa(), sp.getGiaBanHienHanh(), sp.getMaLSP(), sp.getMaSP());
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}
		
		return kq;
	}
	
	public static SanPham LaySanPhamByMa(int ma) {
		SanPham tmp = null;
		KetNoi cn = null;
		
		try {
			cn = new KetNoi();
			String sql = "SELECT * FROM SanPham WHERE MaSP =" + ma;
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int masp = rs.getInt("MaSP");
				String ten = rs.getString("TenSP");
				int slt = rs.getInt("SLTon");
				int slttt = rs.getInt("SLTonToiThieu");
				int tgbh = rs.getInt("ThoiGianBaoHanh");
				String mota = rs.getString("MoTa");
				Float giaban = rs.getFloat("GiaBanHienHanh");
				int malsp = rs.getInt("MaLSP");
				tmp = new SanPham(masp, ten, slt, slttt, tgbh, mota, giaban, malsp);
			}
		} catch (Exception ex) {
			Logger.getLogger(SanPhamDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}
}
